﻿using System.Linq;
using Dapper;
using NewBusiness.Utils;


namespace NewBusiness.Domain.Queries
{
    //заглушка
    public class WorkItem
    {
    }

    public interface IWorkItemsQueries
    {

        //LazyConnection Connection { get; }

        IQueryable<WorkItem> GetWorkItemsForTeam(int id, int page, int itemsPerPage = 10);
        void SetNewPriorityForWorkItem(int id, int beforeId, int afterId);
        void SetWorkItemToUser(int workItemId, int userId);
    }

    public class WorkItemsQueries : Queries, IWorkItemsQueries
    {

        //private LazyConnection Connection { get; set; }

        public WorkItemsQueries(LazyConnection lc) : base(lc) { }

        public IQueryable<WorkItem> GetWorkItemsForTeam(int id, int page, int itemsPerPage = 10)
        {
            IQueryable<WorkItem> workItems;
            var sql = string.Empty;
            if (id > 0)
            {
                sql = @"select  pt.Id, wi.CreatedOn, wi.LastUpdatedOn, wi.ParentId, wi.ChainId, wi.RootId, wi.[Subject], wi.[Description], wi.AuthorId, wi.ResponsibleId, wi.ResponsibleName, wi.CustomerId,
                 wi.CustomerName, wi.TeamId, wi.CurrentStatusId, wi.HoursSpentSoFar, wi.[EstimatedHours], wi.EstimatedEndDate, wi.TaskType, wi.StatusName, pt.[Priority], wi.[Level], 
                 wi.LastStatusChanged, wi.PointCategoryId ,wi.AnalysisNumber	,wi.Tags ,wi.ProjectId  
                 from  ( 
                 select  case  when ch.Id = ch.Priority then p.[Priority] else ch.[Priority]  end as Priority , ch.Id 
                 from [DevTms].[dbo].[WorkItems] as p  inner join [DevTms].[dbo].[WorkItems] as ch on ch.RootId = p.Id  left join [DevTms].[dbo].[WorkItems] as ch1 on ch.Id = ch1.ParentId
                 where ch1.Id is Null and  p.RootId is null
                 union all
                 select p.Priority, p.id from [DevTms].[dbo].[WorkItems] as p left join [DevTms].[dbo].[WorkItems] as ch on ch.ParentId = p.Id
                 where p.RootId is Null and ch.Id is null
                 ) as pt left join [DevTms].[dbo].[WorkItems] as wi on pt.Id = wi.Id
                 where CurrentStatusId in (6, 7, 10, 15, 19) and wi.TeamId = @TeamId
                 and pt.Id not in( select [WorkitemId] from [dbo].[WorkitemQueue])
                 order by [Priority] offset @skipRows row fetch next @itemsPerPage rows only";
            }
            else
            {
                sql = @"select  pt.Id, wi.CreatedOn, wi.LastUpdatedOn, wi.ParentId, wi.ChainId, wi.RootId, wi.[Subject], wi.[Description], wi.AuthorId, wi.ResponsibleId, wi.ResponsibleName, wi.CustomerId,
                 wi.CustomerName, wi.TeamId, wi.CurrentStatusId, wi.HoursSpentSoFar, wi.[EstimatedHours], wi.EstimatedEndDate, wi.TaskType, wi.StatusName, pt.[Priority], wi.[Level], 
                 wi.LastStatusChanged, wi.PointCategoryId ,wi.AnalysisNumber	,wi.Tags ,wi.ProjectId  
                 from  (
                 select  case  when ch.Id = ch.Priority then p.[Priority] else ch.[Priority]  end as Priority , ch.Id from [DevTms].[dbo].[WorkItems] as p  inner join [DevTms].[dbo].[WorkItems] as ch on ch.RootId = p.Id  left join [DevTms].[dbo].[WorkItems] as ch1 on ch.Id = ch1.ParentId
                 where ch1.Id is Null and  p.RootId is null
                 union all
                 select p.Priority, p.id from [DevTms].[dbo].[WorkItems] as p left join [DevTms].[dbo].[WorkItems] as ch on ch.ParentId = p.Id 
                 where p.RootId is Null and ch.Id is null
                 ) as pt left join [DevTms].[dbo].[WorkItems] as wi on pt.Id = wi.Id
                 where CurrentStatusId in (6, 7, 10, 15, 19)
				 and pt.Id not in( select [WorkitemId] from [dbo].[WorkitemQueue])
                 order by [Priority] offset @skipRows row fetch next @itemsPerPage rows only";
            }

            workItems = this.Connection.Value.Query<WorkItem>(sql, new { TeamId = id, skipRows = page * itemsPerPage, itemsPerPage = itemsPerPage }).AsQueryable();

            return workItems;
        }

        public void SetNewPriorityForWorkItem(int id, int beforeId, int afterId)
        {

            string sql = string.Empty;
            if (beforeId > 0)
            {
                sql =
                   @" declare @currentPriority float, @nextPriority float , @resultPriority float
                     set @currentPriority =  (SELECT  wi.[Priority] FROM [DevTms].[dbo].[WorkItems] as wi where wi.Id = @BeforeId);
                     set @nextPriority = ( select min(wi.[Priority]) from [DevTms].[dbo].[WorkItems] as wi where wi.[Priority] > @currentPriority); 
                     set @nextPriority = ISNULL(@nextPriority, @currentPriority + 1 );
                     UPDATE [DevTms].[dbo].[WorkItems] set [Priority] = (@currentPriority + @nextPriority) / 2 where Id = @Id; ";
            }
            else
            {
                sql =
                   @" declare @currentPriority float, @beforePriority float , @resultPriority float
                   set @currentPriority =  (SELECT  wi.[Priority] FROM [DevTms].[dbo].[WorkItems] as wi where wi.Id = @BeforeId);
                   set @beforePriority = ( select max(wi.[Priority]) from [DevTms].[dbo].[WorkItems] as wi where wi.[Priority] < @currentPriority); 
                   set @beforePriority = ISNULL(@beforePriority, @currentPriority + 1 );
                   UPDATE [DevTms].[dbo].[WorkItems] set [Priority] = (@currentPriority + @beforePriority) / 2 where Id = @Id; ";
            }

            this.Connection.Value.Execute(sql, new { Id = id, BeforeId = beforeId != 0 ? beforeId : afterId });
        }

        public void SetWorkItemToUser(int workItemId, int userId)
        {
            var sql =         @"if(select Count(*) from dbo.Workitemqueue as wq where wq.WorkitemId = @WorkItemId) > 0
                                begin 
                                  update  dbo.Workitemqueue 
                                          set UserId = @UserId
                                		  where WorkItemId = @WorkItemId
                                end
                                else 
                                begin
                                  insert into dbo.WorkitemQueue ([WorkitemId]
                                           ,[UserId])
                                		   values(@WorkItemId, @UserId)
                                end";
            this.Connection.Value.Execute(sql, new { WorkItemId = workItemId, UserId = userId});
        }


    }
}
